﻿using Excel2DbTool.Common;
using Excel2DbTool.Helpers;
using Excel2DbTool.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace Excel2DbTool.Migrater_New
{

	public class LJZTableMigrater : SimpleDbTableMigrater
	{
		public static readonly string PrimeKey = SelfFieldNames.LJZID;
		public static readonly string GROUPKEY = OrginTableFieldNames.BUILDNO;

		static List<DbFieldMapping> dbFieldMappings = new List<DbFieldMapping>()
		{
			 //has match
			new OracleTableNumField(OrginTableFieldNames.FULLLAYER,SelfFieldNames.ZCS),
			new OracleTableNotNullStrField(OrginTableFieldNames.BUILDNO,SelfFieldNames.LJZH),

			new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.USE,SelfFieldNames.FWYT1,DbSysDictManager.Instance.FWYTDict),
			new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.STRUCTURE,SelfFieldNames.FWJG1,DbSysDictManager.Instance.FWJGDict),

			 new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.LJZMC,SelfFieldNames.LJZH,(ljzh)=>{
			 return ljzh;
			 }),
			//can not be null,but orgin table has no match；so we will not select relative data from orgin table;
			//but will give a default value to  target table
			new OracleTableIdentityStrField(OrginTableFieldNames.NONE,SelfFieldNames.LJZID,20), //KEY
			new OracleTableReferenceKeyField(OrginTableFieldNames.NONE,SelfFieldNames.ZRZID),
			new OracleTableReferenceKeyField(OrginTableFieldNames.NONE,SelfFieldNames.XMID),
		};

		IList<DbRowMappingNew> zrz_DbRowMappingNewGroup = null;
		DbRowMappingNew xm_dbrm = null;
		public LJZTableMigrater(IList<DbRowMappingNew> zrz_dbrms, DbRowMappingNew xm_dbrm)
		{
			if (zrz_dbrms == null || xm_dbrm == null)
			{
				throw new ArgumentNullException();
			}

			this.zrz_DbRowMappingNewGroup = zrz_dbrms;
			this.xm_dbrm = xm_dbrm;
		}

		protected override IList<DbFieldMapping> DbFieldMappings
		{
			get { return dbFieldMappings; }
		}

		IList<DbRowMappingNew> dbRowMappings;

		public void Execute(Action<string> transactionInsertAction)
		{

			//迁移自然幢下的逻辑幢
			foreach (var zrz_dbrm in zrz_DbRowMappingNewGroup)
			{
				dbRowMappings = new List<DbRowMappingNew>();

				object val_zrzPrimiteKey = zrz_dbrm.GetValByTargetFieldName(ZRZTableMigrater.PrimeKey);

				string sql = GetSelectSqlStr(zrz_dbrm);

				var dt = SqlHelper.GetInstance().Select(sql);

				for (int i = 0; i < dt.Rows.Count; i++)
				{
					DbRowMappingNew dbrm = GetDbRowMapping(val_zrzPrimiteKey);

					for (int j = 0; j < dt.Columns.Count; j++)
					{
						dbrm[j].Val = dt.Rows[i][j];
					}

					dbRowMappings.Add(dbrm);
				}

				string insertSql = GetInsertSqlString();

				transactionInsertAction(insertSql);

				CTableMigrater c = new CTableMigrater(dbRowMappings, zrz_dbrm, xm_dbrm);
				c.Execute(transactionInsertAction);

			}
		}

		string GetSelectSqlStr(DbRowMappingNew zrz_dbrm)
		{
			object xm_projectNameVal = xm_dbrm.GetValByOrginFieldName(XMTableMigrater.GROUPKEY);
			object zrz_buildNoVal = zrz_dbrm.GetValByOrginFieldName(ZRZTableMigrater.GROUPKEY);

			string predicateStr = string.Format("{0} = {1} AND {2} = {3}", ZRZTableMigrater.GROUPKEY, zrz_buildNoVal, XMTableMigrater.GROUPKEY, xm_projectNameVal);
			string format = "SELECT {0} FROM {1} where rowid = any (select max(rowid) from (select * from {1} where {2}) group by {3}) ";

			string sql = string.Format(format, GetFieldStringNeedReslove(), ConfigHelper.TableName, predicateStr, GROUPKEY);

			return sql;
		}


		string GetInsertSqlString()
		{
			if (dbRowMappings.Count == 0)
			{
				return string.Empty;
			}

			StringBuilder sb = new StringBuilder(20000);
			sb.AppendFormat("INSERT INTO {0} ({1})", DbTableNames.BDC_LJZ, GetFieldStringNeedInsert());


			for (int i = 0; i < dbRowMappings.Count(); i++)
			{
				sb.AppendFormat(" SELECT {0} FROM dual {1}", dbRowMappings.ElementAt(i).GetValStr(), (i == dbRowMappings.Count() - 1) ? "" : "union");
			}

			return sb.ToString();
		}

		private DbRowMappingNew GetDbRowMapping(object val)
		{
			DbRowMappingNew dbrm = new DbRowMappingNew();

			foreach (var item in dbFieldMappings)
			{
				if (item is OracleTableReferenceKeyField)
				{
					if (item.TargetFieldName == SelfFieldNames.XMID)
					{
						item.Val = xm_dbrm.GetValByTargetFieldName(SelfFieldNames.XMID);
					}
					else
					{
						item.Val = val;
					}

				}
				dbrm.AddFieldMapping(item.Clone());
			}

			return dbrm;
		}



		class SelfFieldNames
		{
			internal static readonly string LJZID = "LJZID";
			internal static readonly string ZRZID = "ZRZID";
			internal static readonly string ZRZBDCDYH = "ZRZBDCDYH";
			internal static readonly string LJZH = "LJZH";
			internal static readonly string LJZMC = "LJZMC";
			internal static readonly string YCJZMJ = "YCJZMJ";
			internal static readonly string YCDXMJ = "YCDXMJ";
			internal static readonly string YCQTMJ = "YCQTMJ";
			internal static readonly string SCJZMJ = "SCJZMJ";
			internal static readonly string SCDXMJ = "SCDXMJ";
			internal static readonly string SCQTMJ = "SCQTMJ";
			internal static readonly string JGRQ = "JGRQ";
			internal static readonly string FWJG1 = "FWJG1";
			internal static readonly string FWJG2 = "FWJG2";
			internal static readonly string FWJG3 = "FWJG3";
			internal static readonly string FWYT1 = "FWYT1";
			internal static readonly string FWYT2 = "FWYT2";
			internal static readonly string FWYT3 = "FWYT3";
			internal static readonly string ZCS = "ZCS";
			internal static readonly string DSCS = "DSCS";
			internal static readonly string DXCS = "DXCS";
			internal static readonly string BZ = "BZ";
			internal static readonly string JZWZT = "JZWZT";
			internal static readonly string CJSJ = "CJSJ";
			internal static readonly string CJR = "CJR";
			internal static readonly string XGSJ = "XGSJ";
			internal static readonly string XGR = "XGR";
			internal static readonly string XMID = "XMID";
		}

	}

	/*
	  -- Create table
	create table BDC_LJZ
	(
	  ljzid     VARCHAR2(36) not null,
	  zrzid     VARCHAR2(36) not null,
	  zrzbdcdyh VARCHAR2(28),
	  ljzh      VARCHAR2(20) not null,
	  ljzmc     VARCHAR2(50) not null,
	  ycjzmj    FLOAT,
	  ycdxmj    FLOAT,
	  ycqtmj    FLOAT,
	  scjzmj    FLOAT,
	  scdxmj    FLOAT,
	  scqtmj    FLOAT,
	  jgrq      DATE,
	  fwjg1     VARCHAR2(4),
	  fwjg2     VARCHAR2(4),
	  fwjg3     VARCHAR2(4),
	  fwyt1     VARCHAR2(4),
	  fwyt2     VARCHAR2(4),
	  fwyt3     VARCHAR2(4),
	  zcs       INTEGER,
	  dscs      INTEGER,
	  dxcs      INTEGER,
	  bz        VARCHAR2(1000),
	  jzwzt     VARCHAR2(4),
	  cjsj      DATE,
	  cjr       VARCHAR2(50),
	  xgsj      DATE,
	  xgr       VARCHAR2(50),
	  xmid      VARCHAR2(36)
	)
	tablespace BDCDJ0616
	  pctfree 10
	  initrans 1
	  maxtrans 255
	  storage
	  (
		initial 128K
		next 8K
		minextents 1
		maxextents unlimited
	  );
	-- Add comments to the columns 
	comment on column BDC_LJZ.ljzid
	  is '逻辑幢ID';
	comment on column BDC_LJZ.zrzid
	  is '自然幢ID';
	comment on column BDC_LJZ.zrzbdcdyh
	  is '自然幢不动产单元号';
	comment on column BDC_LJZ.ljzh
	  is '逻辑幢号';
	comment on column BDC_LJZ.ljzmc
	  is '逻辑幢名称';
	comment on column BDC_LJZ.ycjzmj
	  is '预测建筑面积';
	comment on column BDC_LJZ.ycdxmj
	  is '预测地下面积';
	comment on column BDC_LJZ.ycqtmj
	  is '预测其他面积';
	comment on column BDC_LJZ.scjzmj
	  is '实测建筑面积';
	comment on column BDC_LJZ.scdxmj
	  is '实测地下面积';
	comment on column BDC_LJZ.scqtmj
	  is '实测其他面积';
	comment on column BDC_LJZ.jgrq
	  is '竣工日期';
	comment on column BDC_LJZ.fwjg1
	  is '房屋结构1';
	comment on column BDC_LJZ.fwjg2
	  is '房屋结构2';
	comment on column BDC_LJZ.fwjg3
	  is '房屋结构3';
	comment on column BDC_LJZ.fwyt1
	  is '房屋用途1';
	comment on column BDC_LJZ.fwyt2
	  is '房屋用途2';
	comment on column BDC_LJZ.fwyt3
	  is '房屋用途3';
	comment on column BDC_LJZ.zcs
	  is '总层数';
	comment on column BDC_LJZ.dscs
	  is '地上层数';
	comment on column BDC_LJZ.dxcs
	  is '地下层数';
	comment on column BDC_LJZ.bz
	  is '备注';
	comment on column BDC_LJZ.jzwzt
	  is '建筑物状态';
	comment on column BDC_LJZ.cjsj
	  is '创建时间';
	comment on column BDC_LJZ.cjr
	  is '创建人';
	comment on column BDC_LJZ.xgsj
	  is '修改时间';
	comment on column BDC_LJZ.xgr
	  is '修改人';
	comment on column BDC_LJZ.xmid
	  is '项目id';
	-- Create/Recreate primary, unique and foreign key constraints 
	alter table BDC_LJZ
	  add constraint N_BDC_LJZ_LJZID_PRI primary key (LJZID)
	  using index 
	  tablespace BDCDJ0616
	  pctfree 10
	  initrans 2
	  maxtrans 255
	  storage
	  (
		initial 64K
		next 1M
		minextents 1
		maxextents unlimited
	  );

 
	 */

}
